# 封装execl表操作的类
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
import os

class ExeclTool:
    def __init__(self,execl_file_path,sheet_name):
        self.execl_file_path = execl_file_path      # execl文件路径
        self.sheet_name = sheet_name
        self.df = None
        self.workbook = None
        self.worksheet = None
        self.execl_file_is_exist()

    # 判断excel文件是否存在 创建pandas和openpyxl实例
    def execl_file_is_exist(self):
        try:
            if os.path.exists(self.execl_file_path):
                # pandas
                pd_execl = pd.ExcelFile(self.execl_file_path)
                pd_sheets = pd_execl.sheet_names
                if self.sheet_name in pd_sheets:    # 判断sheet是否存在
                    self.df = pd.read_excel(self.execl_file_path, sheet_name=self.sheet_name)  # pandas的实例对象
                else:
                    print(f"sheet[{self.sheet_name}]在execl表中不存在，请检查！")

                # openpyxl
                self.workbook = load_workbook(self.execl_file_path)  # 加载execl
                op_sheets = self.workbook.sheetnames
                if self.sheet_name in op_sheets:
                    self.worksheet = self.workbook[self.sheet_name]  # openpyxl要操作的表名
                else:
                    print(f"sheet[{self.sheet_name}]在execl表中不存在，请检查！")
            else:
                print(f"[{self.execl_file_path}]execl文件不存在！请检查")

        except Exception as e:
            print(f"检查文件是否存在时出现异常，异常内容为：{e}")

    # 获取execl的数据长度 包含表头
    def get_execl_len(self):
        print(f"读取到多少行数据：{len(self.df)}")
        return len(self.df)

    # 获取单元格的数据 pandas索引从0开始
    def get_execl_cell(self,row_index,col_name):
        try:
            cell = self.df.at[row_index,col_name]
            return cell

        except Exception as e:
            print(f"获取execl的[{col_name}]发生异常：{e}")

    # 获取列所在的下标
    def get_execl_col_index(self,col_name):
        col_index = self.df.columns.get_loc(col_name)   # 从数据开始读取 不包含表头 pandas下标是从0开始，execl下标是从1开始
        return col_index

    # 更新单元格的数据
    def update_execl_cell(self,row_index,col_index,value):
        self.worksheet.cell(row=row_index,column=col_index).value = value   # 这里的索引要传execl对应的索引数据
        self.workbook.save(self.execl_file_path)    # 保存execl表

    # 填充单元格颜色
    def cell_fill_color(self,row_index,col_index,color):
        self.worksheet.cell(row_index,col_index).fill = PatternFill(fill_type='solid',fgColor=color)
        self.workbook.save(self.execl_file_path)  # 保存execl表

    # 填充整行颜色
    def row_fill_color(self,row_index,color):
        for col in range(row_index,self.worksheet.max_column+1):
            self.worksheet[row_index,col].fill = PatternFill(fill_type='solid', fgColor=color)
            self.workbook.save(self.execl_file_path)  # 保存execl表


if __name__ == '__main__':
    execl_file_path = "D:\python_pj\playwright_test\execl\execl_page_url_test.xlsx"
    sheet_name = 'page_url'

    ex = ExeclTool(execl_file_path,sheet_name)
    page_url = ex.get_execl_cell(1,'页面路径')
    print(f"获取到的页面路径是:{page_url}")


    load_time_index = ex.get_execl_col_index("页面加载时长") + 1 # pandas的索引从0开始，+1才等于execl的索引
    print(f"页面加载时长所在列是：{load_time_index}")
    # ex.update_execl_cell(2,load_time_index,"10s")
    load_time = ex.get_execl_cell(0,'页面加载时长')
    print(f"获取到的页面加载时长是:{load_time}")



